/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.mlu.jdbc.simple.utils;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Mlungisi Sincuba
 * @since 1.0
 */
public abstract class JdbcUtils {

    public static final int TYPE_UNKNOWN = -2147483648;
    private static final Logger logger = LoggerFactory.getLogger(JdbcUtils.class);

    public static void closeDbRecources(Connection con, Statement statement) {
        closeConnection(con);
        closeStatement(statement);
    }
    
    public static void closeDbRecources(Connection con, Statement statement, ResultSet resultSet) {
        closeDbRecources(con, statement);
        closeResultSet(resultSet);
    }
    
    public static void closeConnection(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
                logger.debug("Could not close JDBC Connection", ex);
            } catch (Throwable ex) {
                logger.debug("Unexpected exception on closing JDBC Connection", ex);
            }
        }
    }

    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                logger.trace("Could not close JDBC Statement", ex);
            } catch (Throwable ex) {
                logger.trace("Unexpected exception on closing JDBC Statement", ex);
            }
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.trace("Could not close JDBC ResultSet", ex);
            } catch (Throwable ex) {
                logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
            }
        }
    }

    public static Object getResultSetValue(ResultSet rs, int index, Class requiredType)
            throws SQLException {
        if (requiredType == null) {
            return getResultSetValue(rs, index);
        }

        Object value = null;
        boolean wasNullCheck = false;

        if (String.class.equals(requiredType)) {
            value =  rs.getString(index);
        } else if ((Boolean.TYPE.equals(requiredType)) || (Boolean.class.equals(requiredType))) {
            value =  Boolean.valueOf(rs.getBoolean(index));
            wasNullCheck = true;
        } else if ((Byte.TYPE.equals(requiredType)) || (Byte.class.equals(requiredType))) {
            value =  Byte.valueOf(rs.getByte(index));
            wasNullCheck = true;
        } else if ((Short.TYPE.equals(requiredType)) || (Short.class.equals(requiredType))) {
            value =  Short.valueOf(rs.getShort(index));
            wasNullCheck = true;
        } else if ((Integer.TYPE.equals(requiredType)) || (Integer.class.equals(requiredType))) {
            value =  Integer.valueOf(rs.getInt(index));
            wasNullCheck = true;
        } else if ((Long.TYPE.equals(requiredType)) || (Long.class.equals(requiredType))) {
            value =  Long.valueOf(rs.getLong(index));
            wasNullCheck = true;
        } else if ((Float.TYPE.equals(requiredType)) || (Float.class.equals(requiredType))) {
            value =  Float.valueOf(rs.getFloat(index));
            wasNullCheck = true;
        } else if ((Double.TYPE.equals(requiredType)) || (Double.class.equals(requiredType))
                || (Number.class.equals(requiredType))) {
            value =  Double.valueOf(rs.getDouble(index));
            wasNullCheck = true;
        } else if (java.sql.Date.class.equals(requiredType)) {
            value =  rs.getDate(index);
        } else if (Time.class.equals(requiredType)) {
            value =  rs.getTime(index);
        } else if ((Timestamp.class.equals(requiredType)) || (java.util.Date.class.equals(requiredType))) {
            value =  rs.getTimestamp(index);
        } else if (BigDecimal.class.equals(requiredType)) {
            value =  rs.getBigDecimal(index);
        } else if (Blob.class.equals(requiredType)) {
            value =  rs.getBlob(index);
        } else if (Clob.class.equals(requiredType)) {
            value =  rs.getClob(index);
        } else {
            value = getResultSetValue(rs, index);
        }

        if ((wasNullCheck) && (value != null) && (rs.wasNull())) {
            value = null;
        }
        return value;
    }

    public static Object getResultSetValue(ResultSet rs, int index)
            throws SQLException {
        Object obj = rs.getObject(index);
        String className = null;
        if (obj != null) {
            className = obj.getClass().getName();
        }
        if ((obj instanceof Blob)) {
            obj =  rs.getBytes(index);
        } else if ((obj instanceof Clob)) {
            obj =  rs.getString(index);
        } else if ((className != null) && (("oracle.sql.TIMESTAMP".equals(className))
                || ("oracle.sql.TIMESTAMPTZ".equals(className)))) {
            obj =    rs.getTimestamp(index);
        } else if ((className != null) && (className.startsWith("oracle.sql.DATE"))) {
            String metaDataClassName = rs.getMetaData().getColumnClassName(index);
            if (("java.sql.Timestamp".equals(metaDataClassName))
                    || ("oracle.sql.TIMESTAMP".equals(metaDataClassName))) {
                obj =    rs.getTimestamp(index);
            } else {
                obj =  rs.getDate(index);
            }
        } else if ((obj != null) && ((obj instanceof java.sql.Date))
                && ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index)))) {
            obj =    rs.getTimestamp(index);
        } else if ((className != null) && (className.startsWith("java.sql.Timestamp"))) {
            obj =  rs.getTimestamp(index);
        }

        return obj;
    }

    public static Object extractDatabaseMetaData(DataSource dataSource)
            throws SQLException {
        Connection con = null;
        try {
            con = dataSource.getConnection();
            if (con == null) {
                throw new IllegalArgumentException("Connection returned by DataSource [" + dataSource + "] was null");
            }
            DatabaseMetaData metaData = con.getMetaData();
            if (metaData == null) {
                throw new IllegalArgumentException("DatabaseMetaData returned by Connection [" + con + "] was null");
            }
            return metaData;
        } catch (SQLException ex) {
            throw new IllegalArgumentException("Error while extracting DatabaseMetaData", ex);
        } catch (AbstractMethodError err) {
            throw new IllegalArgumentException(
                    "JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err);
        } finally {
            closeConnection(con);
        }
    }

    public static boolean supportsBatchUpdates(Connection con) {
        try {
            DatabaseMetaData dbmd = con.getMetaData();
            if (dbmd != null) {
                if (dbmd.supportsBatchUpdates()) {
                    logger.debug("JDBC driver supports batch updates");
                    return true;
                }

                logger.debug("JDBC driver does not support batch updates");
            }
        } catch (SQLException ex) {
            logger.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
        } catch (AbstractMethodError err) {
            logger.debug("JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method", err);
        }
        return false;
    }

    public static String commonDatabaseName(String source) {
        String name = source;
        if ((source != null) && (source.startsWith("DB2"))) {
            name = "DB2";
        } else if (("Sybase SQL Server".equals(source))
                || ("Adaptive Server Enterprise".equals(source))
                || ("ASE".equals(source))
                || ("sql server".equalsIgnoreCase(source))) {
            name = "Sybase";
        }
        return name;
    }

    public static boolean isNumeric(int sqlType) {
        return (-7 == sqlType) || (-5 == sqlType) || (3 == sqlType)
                || (8 == sqlType) || (6 == sqlType) || (4 == sqlType)
                || (2 == sqlType) || (7 == sqlType) || (5 == sqlType)
                || (-6 == sqlType);
    }

    public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex)
            throws SQLException {
        String name = resultSetMetaData.getColumnLabel(columnIndex);
        if ((name == null) || (name.length() < 1)) {
            name = resultSetMetaData.getColumnName(columnIndex);
        }
        return name;
    }

    public static String convertUnderscoreNameToPropertyName(String name) {
        StringBuilder result = new StringBuilder();
        boolean nextIsUpper = false;
        if ((name != null) && (name.length() > 0)) {
            if ((name.length() > 1) && (name.substring(1, 2).equals("_"))) {
                result.append(name.substring(0, 1).toUpperCase());
            } else {
                result.append(name.substring(0, 1).toLowerCase());
            }
            for (int i = 1; i < name.length(); i++) {
                String s = name.substring(i, i + 1);
                if (s.equals("_")) {
                    nextIsUpper = true;
                } else if (nextIsUpper) {
                    result.append(s.toUpperCase());
                    nextIsUpper = false;
                } else {
                    result.append(s.toLowerCase());
                }
            }
        }

        return result.toString();
    }
    
    public static String convertPropertyNameToUnderscoreName(String name) {
        StringBuilder result = new StringBuilder();
        if ((name != null) && (name.length() > 0)) {
            for(int i = 0; i < name.length(); i++) {
                char c = name.charAt(i);
                if (Character.isUpperCase(c)) {
                    result.append("_");
                    String s = String.valueOf(c);
                    result.append(s.toLowerCase());
                } else {
                    result.append(c);
                }
            }
        }
        return result.toString();
    }
    
    public static PreparedStatement getPreparedStatement(Connection connection, String sql, Object ...params) throws SQLException {
        if (connection == null) {
            throw new IllegalArgumentException("Connection cannot be null");
        }
        PreparedStatement ps = connection.prepareStatement(sql);
        if (params == null) {
            return ps;
        }
        int i = 1;
        for (Object o : params) {
            ps.setObject(i, o);
            i++;
        }
        return ps;
    }
    
    public static PreparedStatement getPreparedStatement(Connection connection, String sql) throws SQLException {
        if (connection == null) {
            throw new IllegalArgumentException("Connection cannot be null");
        }
        PreparedStatement ps = connection.prepareStatement(sql);
        
        return ps;
    }
    
    public static Connection getConnection(DataSource dataSource) throws SQLException{
        if (dataSource == null) {
            throw new IllegalArgumentException("DataSource cannot be null");
        }
        return dataSource.getConnection();
    }
}
